use mlua::{Lua, Value as LuaValue};
use sqlx::{
    mysql::{MySqlRow, MySqlTypeInfo}, 
    postgres::{PgRow, PgTypeInfo}, 
    sqlite::{SqliteRow, SqliteTypeInfo}, 
    types::chrono::{DateTime, NaiveDate, NaiveDateTime, NaiveTime, Utc}, 
    Row
};
use deadpool_tiberius::tiberius::{Row as MsSqlRow, ColumnType};
use anyhow::Result;

pub(crate) fn sqlite2lua(lua: &Lua, row: &SqliteRow, index: usize, r#type: &SqliteTypeInfo) -> Result<LuaValue>{
    match r#type.to_string().as_str() {
        "TEXT" => match row.try_get::<Option<String>, _>(index) {
            Ok(Some(value)) => Ok(LuaValue::String(lua.create_string(&value).expect("Failed to create Lua string from TEXT data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(_) => Err(anyhow::anyhow!("Failed to get TEXT value at index {}", index)),

        },
        "REAL" => match row.try_get::<Option<f64>, _>(index) {
                Ok(Some(value)) => Ok(LuaValue::Number(value)),
                Ok(None) => Ok(LuaValue::Nil),
                Err(_) => Err(anyhow::anyhow!("Failed to get REAL value at index {}", index)),
        },
        "INTEGER" => match row.try_get::<Option<i64>, _>(index) {
                Ok(Some(value)) => Ok(LuaValue::Integer(value)),
                Ok(None) => Ok(LuaValue::Nil),
                Err(_) => Err(anyhow::anyhow!("Failed to get INTEGER value at index {}", index)),
        },
        "BLOB" => match row.try_get::<Option<Vec<u8>>, _>(index) {
            Ok(Some(value)) => {
                let blob = lua.create_string(&value).expect("Failed to create Lua string from BLOB data");
                return Ok(LuaValue::String(blob));
            }
            Ok(None) => Ok(LuaValue::Nil),
            Err(_) => Err(anyhow::anyhow!("Failed to get BLOB value at index {}", index))

        },
        "NUMERIC" => match row.try_get::<Option<String>, _>(index) {
            Ok(Some(value)) => {
                let decimal_str = value.to_string();
                Ok(LuaValue::String(lua.create_string(&decimal_str).expect("Failed to create Lua string from NUMERIC data")))
            }
            Ok(None) => Ok(LuaValue::Nil),
            Err(_) => Err(anyhow::anyhow!("Failed to get NUMERIC value at index {}", index)),
        },
        "BOOLEAN" => match row.try_get::<Option<bool>, _>(index) {
            Ok(Some(value)) => Ok(LuaValue::Boolean(value)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(_) => Err(anyhow::anyhow!("Failed to get BOOLEAN value at index {}", index)),
        },
        "DATE" | "TIME" | "DATETIME" => match row.try_get::<Option<String>, _>(index) {
            Ok(Some(value)) => Ok(LuaValue::String(lua.create_string(&value).expect("Failed to create Lua string from DATE/TIME/DATETIME data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(_) => Err(anyhow::anyhow!("Failed to get DATE/TIME/DATETIME value at index {}", index)),
        },
        "NULL" => Ok(LuaValue::Nil),
        _ => Err(anyhow::anyhow!("Unsupported SQLite type: {}", r#type))
    }

}

pub(crate) fn mysql2lua(lua:&Lua, row: &MySqlRow, index: usize, r#type: &MySqlTypeInfo) -> Result<LuaValue>{
    match r#type.to_string().as_str(){
        "BOOLEAN" => match row.try_get::<Option<bool>, _>(index){
            Ok(Some(v)) => Ok(LuaValue::Boolean(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get BOOLEAN value at index {}: {}", index, e))
        },
        "TINYINT UNSIGNED"|"SMALLINT UNSIGNED"|"INT UNSIGNED"|"MEDIUMINT UNSIGNED"|"BIGINT UNSIGNED"|"TINYINT"|"SMALLINT"|"INT"|"MEDIUMINT"|"BIGINT" => match row.try_get::<Option<i64>, _>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INT value at index {}: {}", index, e))
        },
        "FLOAT" | "DOUBLE" => match row.try_get::<Option<f64>,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Number(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get FLOAT/DOUBLE value at index {}: {}", index, e))
        },
        "TIMESTAMP" => match row.try_get::<Option<i64>,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get TIMESTAMP value at index {}: {}", index, e))
        },
        "DATE" => match row.try_get::<Option<NaiveDate>, _>(index) {
            Ok(Some(d)) => {
                let d_str = d.format("%Y-%m-%d").to_string();
                Ok(LuaValue::String(lua.create_string(&d_str).expect("Failed to create Lua string from DATE data")))
            },
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get DATE value at index {}: {}", index, e))
        },
        "TIME" => match row.try_get::<Option<NaiveTime>, _>(index) {
            Ok(Some(t)) => {
                let t_str = t.format("%H:%M:%S").to_string();
                Ok(LuaValue::String(lua.create_string(&t_str).expect("Failed to create Lua string from TIME data")))
            },
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get TIME value at index {}: {}", index, e))
        },
        "DATETIME" => match row.try_get::<Option<NaiveDateTime>, _>(index) {
            Ok(Some(dt)) => {
                let dt_str = dt.format("%Y-%m-%d %H:%M:%S").to_string();
                Ok(LuaValue::String(lua.create_string(&dt_str).expect("Failed to create Lua string from DATETIME data")))
            },
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get DATETIME value at index {}: {}", index, e))
        },
        "DECIMAL" => todo!("DECIMIAL type need to be implmented"),
        "BINARY"|"ENUM"
        |"VARBINARY"|"CHAR"|"VARCHAR"
        |"TEXT"|"TINYTEXT"|"MEDIUMTEXT"|"LONGTEXT"
         => match row.try_get::<Option<String>,_>(index){
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v).expect("Failed to create Lua string from DATE/TIME/DATETIME/YEAR data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get String at index {}: {}", index, e))
        },
        "TINYBLOB"|"BLOB"|"MEDIUMBLOB"|"LONGBLOB" => match row.try_get::<Option<Vec<u8>>, _>(index){
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v).expect("Fail to create Lua String from BLOB data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get BLOB value at index: {}, {}", index, e))
        }, 
        "NULL" => Ok(LuaValue::Nil),
        _ => Err(anyhow::anyhow!("Unsupported MySql type: {}", r#type))
    }
}

pub(crate) fn postgres2lua(lua:&Lua, row: &PgRow, index: usize, r#type: &PgTypeInfo) -> Result<LuaValue>{
    match r#type.to_string().as_str(){
        "BOOL" => match row.try_get::<Option<bool>, _>(index){
            Ok(Some(v)) => Ok(LuaValue::Boolean(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get BOOL at index {}: {}", index, e))
        },
        "CHAR"|"\"CHAR\""|"VARCHAR"|"TEXT" => match row.try_get::<Option<String>, _>(index) {
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v).expect("Failed to create Lua string from TEXT data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get String at index {}: {}", index, e))
        },
        "INT2" => match row.try_get::<Option<i16>,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v as i64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INT2 value at index {}: {}", index, e))
        },
        "INT4" => match row.try_get::<Option<i32>,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v as i64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INT4 value at index {}: {}", index, e))
        },
        "INT8" => match row.try_get::<Option<i64>,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INT8 value at index {}: {}", index, e))
        },
        "FLOAT4" => match row.try_get::<Option<f32>, _>(index){
            Ok(Some(v)) => Ok(LuaValue::Number(v as f64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get FLOAT4 value at index {}: {}", index, e))
        }
        "FLOAT8" => match row.try_get::<Option<f64>, _>(index){
            Ok(Some(v)) => Ok(LuaValue::Number(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get FLOAT value at index {}: {}", index, e))
        },
        "TIMESTAMP" => match row.try_get::<Option<NaiveDateTime>, _>(index) {
            Ok(Some(dt)) => {
                let dt_str = dt.format("%Y-%m-%d %H:%M:%S.%3f").to_string();
                Ok(LuaValue::String(lua.create_string(&dt_str).expect("Failed to create Lua string from TIMESTAMPTZ data")))
            },
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get TIMESTAMP value at index {}: {}", index, e))
        },
        "TIMESTAMPTZ" => match row.try_get::<Option<DateTime<Utc>>, _>(index) {
            Ok(Some(dt)) => {
                let dt_str = dt.format("%Y-%m-%d %H:%M:%S.%3f").to_string();
                Ok(LuaValue::String(lua.create_string(&dt_str).expect("Failed to create Lua string from TIMESTAMP data")))
            },
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get TIMESTAMPZ value at index {}: {}", index, e))
        },
        "DATE" => match row.try_get::<Option<NaiveDate>, _>(index){
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v.format("%Y-%m-%d").to_string()).expect("Failed to create Lua string from DATE data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get DATE value at index {}: {}", index, e))
        },
        _ => Err(anyhow::anyhow!("Unsupported Postgres type: {}", r#type))
    }
}


pub(crate) fn mssql2lua(lua: &Lua, row: &MsSqlRow, index: usize, r#type: &ColumnType) -> Result<LuaValue>{
    match r#type {
        ColumnType::Int1 => match row.try_get::<i16,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v as i64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INTEGER value on index {}: {}", index, e))
        },
        ColumnType::Int2 => match row.try_get::<i16,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v as i64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INTEGER value on index {}: {}", index, e))
        },
        ColumnType::Int4 => match row.try_get::<i32,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v as i64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INTEGER value on index {}: {}", index, e))
        },
        ColumnType::Int8 => match row.try_get::<i64,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Integer(v as i64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get INTEGER value on index {}: {}", index, e))
        },
        ColumnType::Float4 => match row.try_get::<f32,_>(index){
            Ok(Some(v)) => Ok(LuaValue::Number(v as f64)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get FLOAT value on index {}: {}", index, e))
        },
        ColumnType::Float8 => match row.try_get::<f64, _>(index) {
            Ok(Some(v)) => Ok(LuaValue::Number(v)),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get FLOAT value on index {}: {}", index, e))
        },
        ColumnType::Text
        |ColumnType::NChar
        |ColumnType::NText
        |ColumnType::NVarchar
        |ColumnType::BigVarChar
        |ColumnType::BigChar => match row.try_get::<&str, _>(index) {
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v).expect("Failed to create Lua string from TEXT data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get String value on index {}: {}", index, e))        
        },
        ColumnType::Datetime => match row.try_get::<NaiveDateTime, _>(index) {
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v.format("%Y-%m-%d %H:%M:%S.%3f").to_string()).expect("Failed to create Lua string from DATETIME data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get DATETIME value on index {}: {}", index, e))
        },
        ColumnType::Datetime2 => match row.try_get::<NaiveDateTime, _>(index) {
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v.format("%Y-%m-%d %H:%M:%S.%3f").to_string()).expect("Failed to create Lua string from DATETIME data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get DATETIME2 value on index {}: {}", index, e))
        },
        ColumnType::Datetimen => match row.try_get::<NaiveDateTime, _>(index) {
            Ok(Some(v)) => Ok(LuaValue::String(lua.create_string(&v.format("%Y-%m-%d %H:%M:%S.%3f").to_string()).expect("Failed to create Lua string from DATETIME data"))),
            Ok(None) => Ok(LuaValue::Nil),
            Err(e) => Err(anyhow::anyhow!("Fail to get DATETIMEN value on index {}: {}", index, e))
        }
        ColumnType::Null => Ok(LuaValue::Nil),
        _ => Err(anyhow::anyhow!("Unsupport MsSql type: {:?}", r#type))

        
    }
}